Namespace Marcusoft.Utils.ScrumIndexCardCreator
    ''' <summary>
    ''' This form lets the user load a backlog item file
    ''' and select items to print from it
    ''' </summary>
    ''' <remarks></remarks>
    Public Class Main

#Region " Private variables "
        Dim dsExcel As DataSet
        Dim m_fileName As String = String.Empty
#End Region

#Region " Private methods "
        ''' <summary>
        ''' Loads the name of the sheets in the excelfile into the combox
        ''' </summary>
        ''' <param name="excelFilePath">the path to the excelfile</param>
        ''' <remarks></remarks>
        Private Sub LoadExcelSheetsIntoCombo(ByVal excelFilePath As String)
            Dim con As OleDb.OleDbConnection = Nothing
            Try

                ' Re-create the dataset of the form
                Me.dsExcel = New DataSet()

                ' In order to get Excel to skip converting no-formatted data into null
                ' we have to include IMEX=1 as an extended property (see http://geekswithblogs.net/lbugnion/archive/2006/08/25/89315.aspx)
                ' also the extended properties need to be included in quotes (see http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx)
                con = New OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;IMEX=1""", excelFilePath))

                ' Get the data from the Excel file through a query on all mandatory columns
                Dim daExcel As New OleDb.OleDbDataAdapter()

                con.Open()

                ' Get a list of the sheets in the excel-book
                Dim excelSheets As DataTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

                ' Create items for each sheet
                cmbSheets.Items.Clear()
                Dim s As String
                For Each dr As DataRow In excelSheets.Rows
                    s = dr("TABLE_NAME").ToString()
                    cmbSheets.Items.Add(s)
                Next
            Finally
                If con.State <> ConnectionState.Closed Then
                    con.Close()
                End If
            End Try
        End Sub

        ''' <summary>
        ''' Loads the contenten of the sheet into the grid 
        ''' </summary>
        ''' <param name="excelFilePath">the path to the excelfile</param>
        ''' <param name="sheetName">the name of the sheet to load</param>
        ''' <remarks></remarks>
        Private Sub LoadExcelDataIntoGrid(ByVal excelFilePath As String, ByVal sheetName As String)
            Dim con As OleDb.OleDbConnection = Nothing
            Try

                ' Re-create the dataset of the form
                Me.dsExcel = New DataSet()

                ' 2007-04-10 MAHA72: In order to get Excel to skip converting no-formatted data into null
                ' we have to include IMEX=1 as an extended property (see http://geekswithblogs.net/lbugnion/archive/2006/08/25/89315.aspx)
                ' also the extended properties need to be included in quotes (see http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx)
                con = New OleDb.OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;IMEX=1""", excelFilePath))

                ' Get the data from the Excel file through a query on all mandatory columns
                Dim daExcel As New OleDb.OleDbDataAdapter()

                con.Open()

                ' Get a list of the sheets in the excel-book
                Dim excelSheets As DataTable = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})

                ' Get the name of the first sheet
                sheetName = "[" & sheetName & "]"

                ' Ta bort eventuella _ i slutet
                If sheetName.EndsWith("_]") Then
                    sheetName = sheetName.Replace("_]", "]")
                End If

                ' Create select statment 
                daExcel.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM " & sheetName, con)

                ' Fill dataset with all data from the Excel-file; do not re-sort or exclude any rows
                daExcel.Fill(Me.dsExcel)

                ' Databind the dataset to the grid
                grdExcelView.DataSource = Me.dsExcel.Tables(0)

            Catch ex As Exception
                MessageBox.Show(ex.ToString())
            Finally
                If con.State <> ConnectionState.Closed Then
                    con.Close()
                End If
            End Try
        End Sub

        ''' <summary>
        ''' Opens a new backlog item file
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub OpenBackLogItemFile()
            ' Let the user select the file
            OpenFileDialog1.FileName = ""
            m_fileName = String.Empty
            If (OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK) Then
                ' Get the selected file name
                m_fileName = OpenFileDialog1.FileName

                ' Load the file into a dataset
                LoadExcelSheetsIntoCombo(m_fileName)

                cmbSheets.Focus()
            Else
                MessageBox.Show("Please select a file with backlog items")
            End If
        End Sub
#End Region

#Region " Events "

        ''' <summary>
        ''' Runs when the form is loaded
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            '            OpenBackLogItemFile()
        End Sub

        ''' <summary>
        ''' Runs when the close button is clicked
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
            Me.Close()
        End Sub

        ''' <summary>
        ''' Runs when the print button is clicked
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub btnPrintSelected_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrintSelected.Click
            ' Loop over all selected items in the grid
            For Each selectedRow As DataGridViewRow In grdExcelView.SelectedRows

                ' This is the excpected order
                ' 0 - ID	
                ' 1 - Name
                ' 2 - Importance
                ' 3 - Estimate
                ' 4 - How to demo
                ' 5 - Notes
                Dim id As String = selectedRow.Cells(0).Value.ToString()
                Dim name As String = selectedRow.Cells(1).Value.ToString()
                Dim importance As String = selectedRow.Cells(2).Value.ToString()
                Dim estimate As String = selectedRow.Cells(3).Value.ToString()
                Dim howToDemo As String = selectedRow.Cells(4).Value.ToString()
                Dim notes As String = selectedRow.Cells(5).Value.ToString()

                Dim ic As New IndexCard(id, name, importance, estimate, howToDemo, notes)

                ic.ShowDialog()

            Next

            ' Show nice message
            MessageBox.Show("Done printing " & grdExcelView.SelectedRows.Count.ToString() & " items")
        End Sub

        ''' <summary>
        ''' When the button load is clicked
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click
            OpenBackLogItemFile()
        End Sub

        ''' <summary>
        ''' Runs when a sheet is selected
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub cmbSheets_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSheets.SelectedIndexChanged
            ' Get the name of the sheet from the dropdown
            Dim sheetName As String = cmbSheets.SelectedItem.ToString()

            ' Load the contents of the sheet into the grid
            Me.LoadExcelDataIntoGrid(m_fileName, sheetName)

            ' Enable the printbutton
            btnPrintSelected.Enabled = True

        End Sub
#End Region
    End Class
End Namespace